clear all
set type double
set varabbrev on, perm

*** FILL IN MAIN PATH TO RUN CODE ***
global dropbox "..."

global folder "$dropbox/_0_bjps_replication"
global in "$folder/in"
global out "$folder/out"
global proc "$folder/proc"

global graph "$out/graph"
global table "$out/table"
global temp "`c(tmpdir)'"

capture ssc install statastates

*** Table 1: PPP columns ***
**********************************************
import delimited using "$in/PPP sample with matching indicator.csv", varname(1)clear

g count = 1

replace businesstype = "Other" if !inlist(businesstype, "Corporation", "Limited  Liability Company(LLC)", "Sole Proprietorship")

g term_cat = "0-24" if inrange(term,0,24)
replace term_cat = "25-48" if inrange(term,25,48)
replace term_cat = "49-60" if inrange(term, 49, 60)
replace term_cat = "Over 60" if term>60

destring forgivenessamount, replace force
foreach v of varlist initialapprovalamount currentapprovalamount forgivenessamount{
	g `v'_cat = "\textdollar0-10K" if inrange(`v', 0, 10000)
	replace `v'_cat = "\textdollar10,001-25K" if `v'> 10000 & `v'<= 25000
	replace `v'_cat = "\textdollar25,001-100K" if `v'> 25000 & `v'<= 100000
	replace `v'_cat = "\textdollar100K+" if `v'> 100000
	replace `v'_cat = "Missing" if mi(`v')
}

local rank = 0
foreach v in loanstatus ruralurbanindicator hubzoneindicator lmiindicator businesstype businessagedescription term_cat initialapprovalamount_cat currentapprovalamount_cat forgivenessamount_cat{
	preserve
		collapse (rawsum)full = count, by(`v')
		g category = "`v'"
		ren `v' label
		capture decode label, g(label2)
		g rank = `rank'
		sa "$temp/`v'", replace
	restore
	
	preserve
		keep if matched == 1
		collapse (rawsum)invited = count, by(`v')
		g category = "`v'"
		ren `v' label
		capture decode label, g(label2)
		g rank = `rank'
		sa "$temp/`v'_2", replace
	restore
	
	local rank = `rank' + 1	
} 

*** survey sample ***
u "$in/combined_survey.dta", clear
keep if sample == "verasight"

g count = 1

rename _all, lower

replace businesstype = "Other" if !inlist(businesstype, "Corporation", "Limited  Liability Company(LLC)", "Sole Proprietorship")

g term_cat = "0-24" if inrange(term,0,24)
replace term_cat = "25-48" if inrange(term,25,48)
replace term_cat = "49-60" if inrange(term, 49, 60)
replace term_cat = "Over 60" if term>60

destring forgivenessamount, replace force
foreach v of varlist initialapprovalamount currentapprovalamount forgivenessamount{
	g `v'_cat = "\textdollar0-10K" if inrange(`v', 0, 10000)
	replace `v'_cat = "\textdollar10,001-25K" if `v'> 10000 & `v'<= 25000
	replace `v'_cat = "\textdollar25,001-100K" if `v'> 25000 & `v'<= 100000
	replace `v'_cat = "\textdollar100K+" if `v'> 100000
	replace `v'_cat = "Missing" if mi(`v')
}

local rank = 1
foreach v in loanstatus ruralurbanindicator hubzoneindicator lmiindicator businesstype businessagedescription term_cat initialapprovalamount_cat currentapprovalamount_cat forgivenessamount_cat{
	preserve
		collapse (rawsum)unweighted = count weighted = weight, by(`v')
		g category = "`v'"
		ren `v' label
		capture decode label, g(label2)
		g rank = `rank'
		sa "$temp/`v'_3", replace
	restore
	
	local rank = `rank' + 1	
} 

***
clear
foreach v in loanstatus ruralurbanindicator hubzoneindicator lmiindicator businesstype businessagedescription term_cat initialapprovalamount_cat currentapprovalamount_cat forgivenessamount_cat{
	append using "$temp/`v'"
}
sa "$temp/full", replace

clear
foreach v in loanstatus ruralurbanindicator hubzoneindicator lmiindicator businesstype businessagedescription term_cat initialapprovalamount_cat currentapprovalamount_cat forgivenessamount_cat{
	append using "$temp/`v'_3", force
}
replace label = label2 if mi(label)
drop label2
sa "$temp/sample", replace

clear
foreach v in loanstatus ruralurbanindicator hubzoneindicator lmiindicator businesstype businessagedescription term_cat initialapprovalamount_cat currentapprovalamount_cat forgivenessamount_cat{
	append using "$temp/`v'_2"
}
merge 1:1 category label* using "$temp/full", nogen
merge 1:1 category label* using "$temp/sample", nogen

foreach v of varlist full invited unweighted weighted{
	bysort category: egen tot_`v' = total(`v')
	replace `v' = `v'/tot_`v'*100
}

g last = label == "Missing"|label == "Other"|label == "Territories"
g last2 = label == "\textdollar100K+"

sort rank last last2 label
drop if mi(label)


********
** latex file **
format invited full *weighted %12.1fc

	g tab = "\begin{tabular}{lrrrr}" in 1
	g top = "\toprule" in 1
	*Midrule
	g mid = " \midrule" in 1 
	g hline = " \hline" in 1 
	*Bottomrule
	g bot = "\bottomrule" in 1
	*End
	g end = "\end{tabular}" in 1
	*Panel

	g title1 = " & PPP(Full) & PPP(Invited) & Sample & Sample \\" in 1 
	g title2 = " &  &  & (Unweighted) & (Weighted) \\" in 1
	g title3 = " & \textit{N = 557,859} & \textit{N = 221,142}& \textit{N = 701} & \textit{N = 701} \\" in 1

	g row1 = "\textit{Loan Status}& & & & \\" in 1
	g row2 = "\textit{Rural or Urban}& & & & \\" in 1
	g row3 = "\textit{Business Hub Zone?}& & & \\" in 1
	g row4 = "\textit{LMI Indicator}& & & & \\" in 1
	g row5 = "\textit{Business Type}& & & & \\" in 1
	g row6 = "\textit{Business Age}& & & & \\" in 1
	g row7 = "\textit{Loan Term}& & & & \\" in 1
	g row8 = "\textit{Initial Approval Amount}& & & & \\" in 1
	g row9 = "\textit{Current Approval Amount}& & & & \\" in 1
	g row10 = "\textit{Forgiveness Amount}& & & & \\" in 1
	
local filename = "desc_ppp"

	listtex tab if _n == 1 using "$table/`filename'.tex", replace rstyle(none)
	listtex hline if _n == 1, appendto("$table/`filename'.tex") rstyle(none)
	listtex title1 if _n == 1, appendto("$table/`filename'.tex") rstyle(none)
	listtex title2 if _n == 1, appendto("$table/`filename'.tex") rstyle(none)
	listtex title3 if _n == 1, appendto("$table/`filename'.tex") rstyle(none)
	listtex hline if _n == 1, appendto("$table/`filename'.tex") rstyle(none)
	
	forval i = 1/10{
		listtex row`i' if _n == 1, appendto("$table/`filename'.tex") rstyle(none)
		listtex label full invited unweighted weighted if rank == `i', appendto("$table/`filename'.tex") rstyle(tabular)
		listtex hline if _n == 1, appendto("$table/`filename'.tex") rstyle(none)
	}

	listtex bot if _n == 1, appendto("$table/`filename'.tex") rstyle(none)	
	listtex end if _n == 1, appendto("$table/`filename'.tex") rstyle(none)

**********************************************
*** Table 2: verasight, prolific sample on observables ***
u "$in/combined_survey.dta", clear
drop if mi(small_biz)

g count = 1

g age =2023-birth_year2

g age_group = 1 if inrange(age, 18,34)
replace age_group = 2 if inrange(age, 35, 54)
replace age_group = 3 if age > 54
label define age_labels 1 "18 to 34" 2 "35 to 54" 3 "55 and above" 
label values age_group age_labels

g race_cat = 1 if race2_1 == 1
replace race_cat = 2 if race2_2 == 1
replace race_cat = 3 if mi(race_cat) & !mi(race2_1)
label define race_labels 1 "White" 2 "Black" 3 "Other" 
label values race_cat race_labels

statastates, name(state_of_residence)
drop if _merge == 2 //DC
drop _merge 

ren state_abbrev borrowerstate
g region= "Midwest" if inlist(borrowerstate, "IA", "IL","IN","KS","MI","MN","MO")
replace region = "Midwest" if inlist(borrowerstate,"ND","NE","OH","SD","WI")
replace region ="Northeast" if inlist(borrowerstate, "CT","MA","ME","NH","NY","RI","VT", "NJ", "PA")
replace region="South" if inlist(borrowerstate, "AL","AR","DC","DE","FL","GA","KY", "OK")
replace region="South" if inlist(borrowerstate,"LA","MD","NC","SC","TX","VA","WV", "MS", "TN")
replace region="West" if inlist(borrowerstate, "AK","AZ","CO","HI","ID","NM","NV")
replace region="West" if inlist(borrowerstate,"OR","UT","WA","WY", "CA", "MT")
replace region ="Territories" if inlist(borrowerstate,"AS","GU","MP","PR","VI")

encode region, g(region_)
drop region
sa "$temp/clean", replace

u "$temp/clean", clear
local rank = 0
foreach v of varlist region political_party age_group gender2 race_cat hispanic_latino2 education total_income{
	preserve
		keep if small_biz == 1
		collapse (rawsum)unweighted = count weighted = weight, by(`v')
		g category = "`v'"
		ren `v' label
		capture decode label, g(label2)
		g rank = `rank'
		sa "$temp/`v'", replace
	restore
	
	preserve
		keep if small_biz != 1
		collapse (rawsum)prolific = count, by(`v')
		g category = "`v'"
		ren `v' label
		capture decode label, g(label2)
		g rank = `rank'
		sa "$temp/`v'_p", replace
	restore
	
	local rank = `rank' + 1
}

preserve
	foreach v of varlist region political_party age_group gender2 race_cat hispanic_latino2 education total_income{
		append using "$temp/`v'_p"
	}
	keep if !mi(category)
	keep category label* prolific rank
	sa "$temp/prolific_desc", replace
restore

foreach v of varlist region political_party age_group gender2 race_cat hispanic_latino2 education total_income{
	append using "$temp/`v'"
}
keep if !mi(category)
keep category label* unweighted weighted rank

merge 1:1 category label* using "$temp/prolific_desc", nogen

foreach v of varlist unweighted weighted prolific{
	bysort category: egen tot_`v' = total(`v')
	replace `v' = `v'/tot_`v'*100
}

sort rank label
drop if mi(label)
replace label2 = "Graduate degree" if label2 == "Graduate degree (e.g. MA, MS, JD, MBA, PhD, etc.)"
replace label2 = usubinstr(label2, "$", " \textdollar",.)

********
** latex file **
format weighted unweighted prolific %12.1fc

	g tab = "\begin{tabular}{lrrr}" in 1
	g top = "\toprule" in 1
	*Midrule
	g mid = " \midrule" in 1 
	g hline = " \hline" in 1 
	*Bottomrule
	g bot = "\bottomrule" in 1
	*End
	g end = "\end{tabular}" in 1
	*Panel

	g title1 = " & SBOs & SBOs & Non-SBOs \\" in 1 
	g title2 = " & (Unweighted) & (Weighted) & \\" in 1
	g title3 = " & \textit{N = 701} & \textit{N = 701}& \textit{N = 1,586} \\" in 1
	
	g row0 = "\textit{Region}& & & \\" in 1
	g row1 = "\textit{Political Party}& & & \\" in 1
	g row2 = "\textit{Age}& & & \\" in 1
	g row3 = "\textit{Gender}& & & \\" in 1
	g row4 = "\textit{Race}& & & \\" in 1
	g row5 = "\textit{Hispanic}& & & \\" in 1
	g row6 = "\textit{Education Level}& & & \\" in 1
	g row7 = "\textit{Income Level}& & & \\" in 1

	
local filename = "desc_observables"

	listtex tab if _n == 1 using "$table/`filename'.tex", replace rstyle(none)
	listtex hline if _n == 1, appendto("$table/`filename'.tex") rstyle(none)
	listtex title1 if _n == 1, appendto("$table/`filename'.tex") rstyle(none)
	listtex title2 if _n == 1, appendto("$table/`filename'.tex") rstyle(none)
	listtex title3 if _n == 1, appendto("$table/`filename'.tex") rstyle(none)
	listtex hline if _n == 1, appendto("$table/`filename'.tex") rstyle(none)
	
	forval i = 0/7{
		listtex row`i' if _n == 1, appendto("$table/`filename'.tex") rstyle(none)
		listtex label2 unweighted weighted prolific if rank == `i', appendto("$table/`filename'.tex") rstyle(tabular)
		listtex hline if _n == 1, appendto("$table/`filename'.tex") rstyle(none)
	}

	listtex bot if _n == 1, appendto("$table/`filename'.tex") rstyle(none)	
	listtex end if _n == 1, appendto("$table/`filename'.tex") rstyle(none)
	
